<?php
// MySQL database utility functions.

require_once('config.php');

//
// Connection functions.
//

function mysql_connect_localhost_readonly($database='') {
	global $larch_db_host, $larch_db_readonly_user;
	global $larch_db_readonly_pass;
        $db = mysql_connect($larch_db_host,
			    $larch_db_readonly_user,
			    $larch_db_readonly_pass);
	if(!$db) {
		echo mysql_error();
	}
        return mysql_connect_localhost_finish($db, $database);
}

function mysql_connect_localhost_readwrite($database='') {
	global $larch_db_host, $larch_db_readwrite_user;
	global $larch_db_readwrite_pass;
        $db = mysql_connect($larch_db_host,
			    $larch_db_readwrite_user,
			    $larch_db_readwrite_pass)
	      or die(mysql_error());
        return mysql_connect_localhost_finish($db, $database);
}

function mysql_connect_localhost_overwrite($database='') {
	global $larch_db_host, $larch_db_overwrite_user;
	global $larch_db_overwrite_pass;
	$db = mysql_connect($larch_db_host,
			    $larch_db_overwrite_user,
			    $larch_db_overwrite_pass)
	      or die(mysql_error());
        return mysql_connect_localhost_finish($db, $database);
}

// Factored out of above functions.
function mysql_connect_localhost_finish($db_link, $database) {
	if(!is_resource($db_link)) die(mysql_error());
	if ($database != '') mysql_select_db($database) or die(mysql_error());
	return $db_link;
}

//
// Canned queries.
//

// Query for user's account type.
function get_user_type($userid, $db_conn) {
	$query = "SELECT usr_type FROM users WHERE usr_id = $userid;";
	$result = mysql_query($query, $db_conn);
	if ($result == false) {
		return false;
	} elseif (mysql_num_rows($result) == 0) {
		return false;
	} else {
		return $result['usr_type'];
	}
}

// Retrieve some list of key-value pairs from the database.
// $query is the query which should return key names and value names.
// $db_conn is the database connection.
// $car is the key column name, $cdr is the value column name.
function get_pair_list($query, $db_conn, $car, $cdr) {
	$result = mysql_query($query, $db_conn);
	if ($result == false) {
		return false;
	} elseif (mysql_num_rows($result) == 0) {
		return false;
	} else {
		$list = array();
		while ($row = mysql_fetch_assoc($result))
			$list[$row[$car]] = $row[$cdr];
		return $list;
	}
}

function get_countries($db_conn) {
	$query = "SELECT alpha2, name FROM ISO_3166 ORDER BY name;";
	return get_pair_list($query, $db_conn, 'alpha2', 'name');
}

function get_institutions($db_conn) {
     $query = "SELECT inst_id, inst_name FROM institutions ORDER BY inst_name;";
	return get_pair_list($query, $db_conn, 'inst_id', 'inst_name');
}

//
// Query building functions.
//

// Makes a select clause. Argument is an array of column=>value pairs. Ignores
// the values, and uses the columns for a SELECT column list.
function mysql_build_select_clause ($arr) {
	if (count($arr) == 0)
		return false;
	$query = "SELECT ";
	$comma = false;
	foreach ($arr as $c => $v) {
		// We ignore $v.
		if ($comma) $query .= ", ";
		$query .= "$c";
		$comma = true;
	}
	$query .= " \n";

	return $query;
}

// Makes an SQL FROM clause. Argument $table is a table name.
function mysql_build_from_clause ($table) {
	if (strlen($table) != 0) {
		return "FROM $table \n";
	} else {
		return false;
	}
}

// Makes an SQL WHERE clause. $arr is an array of column=>value pairs.
// $ignore_empties determines whether empty values are ignored.
// Only does equality '=', not 'LIKE'.
function mysql_build_where_eq_clause ($arr, $ignore_empties) {
	if (count($arr) == 0)
		return false;
	$query = "WHERE ";
	$and = false;
	foreach ($arr as $k => $v) {
		// If $ignore_empties is true then ignore empty values.
		if (!$ignore_empties and $v != '') {
			if ($and) $query .= "\n  AND ";
			$query .= "$k = '$v'";
			$and = true;
		}
	}

	// NOTE: We don’t stick a space on the end, it’s usually unnecessary.
	return $query;
}

// Makes an SQL WHERE clause. Argument is an array of column=>value pairs.
// Only does LIKE, not equality.
function mysql_build_where_like_clause ($arr) {
	if (count($arr) == 0)
		return false;
	$query = "WHERE ";
	$and = false;
	foreach ($arr as $c => $v) {
		if ($and) $query .= "\n  AND ";
		$query .= "$c LIKE '$v'";
		$and = true;
	}

	// NOTE: We don’t stick a space on the end, it’s usually unnecessary.
	return $query;
}

// Builds an SQL query from a table name and an array of column=>value pairs.
// SELECTs specific column names from the array. The WHERE clause tests for
// column = 'value', and doesn’t do LIKE matching. Error checking is done by
// the called functions.
function mysql_build_eq_query ($table, $arr, $ignore_empties) {
	$query = mysql_build_select_clause($arr);
	$query .= mysql_build_from_clause($table);
	$query .= mysql_build_where_eq_clause($arr, $ignore_empties);
	return $query;
}

// Builds an SQL query from a table name and an array of column=>value pairs.
// SELECTs specific column names from the array. The WHERE clause tests for
// column LIKE 'value'. Error checking is done by the called functions.
function mysql_build_like_query ($table, $arr) {
	$query = mysql_build_select_clause($arr);
	$query .= mysql_build_from_clause($table);
	$query .= mysql_build_where_like_clause($arr);
	return $query;
}

//
// Error handling.
//

// Returns a string with error number and error string.
function mysql_errno_error () {
	return mysql_errno() . ": " . mysql_error();
}

//
// Miscellany.
//

// The next two functions are for database column names. In the database the
// column names usually have prefixes like "pfx_...". In the front end we don't
// want these, so these functions are for adding and removing these prefixes.

function remove_prefix($pairs, $prefix) {
	if ($prefix == "") return false;
	// Strip the trailing underscore from $prefix.
	$prefix = rtrim($prefix, "_");
	// Loop over all pairs.
	foreach ($pairs as &$k => $v) {
		// Parse into array of nonunderscore segments.
		$parts = explode($k, "_");
		// Remove initial segment if it's $prefix.
		if ($parts[0] == $prefix) {
			$k = implode("_", array_slice($parts, 1))
		}
	}
	return $rows;
}

function add_prefix($pairs, $prefix) {
	if ($prefix == "") return false;
	// Strip the trailing underscore from $prefix.
	$prefix = rtrim($prefix, "_");
	// Loop over all pairs.
	foreach ($pairs as &$k => $v) {
		// Parse into array of nonunderscore segments.
		$parts = explode($k, "_");
		// Add prefix to the beginning if it doesn't exist.
		if ($parts[0] != $prefix) {
			array_unshift($parts, $prefix);
			$k = implode("_", $parts);
		}
	}
	return $rows;
}

?>
